from sqlalchemy.dialects.mysql import LONGTEXT
import pandas as pd
import os
from sqlalchemy import create_engine,MetaData
from sqlalchemy_utils import create_database,database_exists
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Table,Column,Integer,ForeignKey,String,Text,FLOAT,VARCHAR
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.ext.declarative import declarative_base
import numpy as np
def read_filelist(path):
    """
    读取文件列表
    :param path: 文件外层路径
    :return: 读取的文件名列表
    """
    return os.listdir(path)
def filter_dir_list(filelist:list):
    """
    删除列表中非A股的股票代码
    :param filelist: read_filelist()函数的返回值
    :return: 筛选之后的文件名列表
    """
    for stkcd in filelist:
        if stkcd[0]=='2' or stkcd[0]=='4':
            #print(type(stkcd[0]))
            filelist.remove(stkcd)
    return filelist
def check_code(save_path):
    if not os.path.exists(save_path + '\\' + '额外空页二次检查' + '\\' + 'check_code.txt'):
        #os.makedirs(save_path + '\\' + '额外空页二次检查' + '\\' + 'check_code.txt')
        with open(save_path + '\\' + '额外空页二次检查' + '\\' + 'check_code.txt') as f:
            f.close()
def sql_fail(save_path,stkcd):
    with open(save_path + '\\' + '额外空页二次检查' + '\\' + 'upload_fail.txt', 'a') as f:
        f.write('\n{}'.format(stkcd))
        f.close()

def merge_boards(stkcd:str,save_path,path):
    stkcd_path=path+'\\'+stkcd
    #print(stkcd,stkcd_path)
    csvs=os.listdir(stkcd_path)
    print(csvs)
    for csv in csvs:
        if csv[-6:-4]=="空页":
            with open(save_path + '\\' + '额外空页二次检查' + '\\' + 'check_code.txt','a') as f:
                f.write('\n{}'.format(stkcd))
                f.close()
            csvs.remove(csv)
    dataframe=pd.DataFrame()
    for csv in csvs:
        data=pd.read_csv(stkcd_path+'\\'+csv)
        #print(data.head())
        dataframe=pd.concat([dataframe,data],axis=0)
        #print('dataframe',dataframe)
    dataframe['text']=np.nan
    header='http://guba.eastmoney.com'
    length=len(dataframe)
    index = dataframe.iloc[:, 0]
    fullLink=header+dataframe['link'].astype('str')
    dataframe['link']=fullLink
    #dataframe=dataframe.iloc[:,:-2]
    dataframe = dataframe.iloc[:, 1:]
    #print(dataframe.shape)
    dataframe=dataframe.reset_index(drop=True)
    dataframe = dataframe.reset_index()
    #print(dataframe.head(100))
    return dataframe
def save_to_sql(dataframe:pd.DataFrame,mode='append'):
    engine = create_engine('mysql+pymysql://root:a123456a@localhost:3306/firm', echo=False)
    db = SQLAlchemy()
    dtypedict = {

        'stkcd': VARCHAR(150),

        'title': VARCHAR(150),

        'link':  VARCHAR(4000),

        '公告类型':  VARCHAR(150),

        'enddate':  VARCHAR(4000),

        'text':  LONGTEXT(),

        'index': Integer()
    }
    dataframe=dataframe.fillna(value='未知')
    dataframe['text'] = np.nan
    dataframe.to_sql(name='base_imformation',con=engine,index=False,if_exists=mode, dtype=dtypedict)
if __name__=="__main__":
    path='G:\企业公告'
    #stkcd='000001'
    save_path='D:\公司年报爬取\组合后企业公告'
    filelist=read_filelist(path)
    filelist=filter_dir_list(filelist)
    check_code(save_path)
    for stkcd in filelist:
        #print(stkcd)
        files=merge_boards(stkcd,save_path,path)
        #files.to_excel('检查.xlsx')
        #print('形状',files.shape)
        #print('形状', files.columns)
        try:
            if stkcd==filelist[0]:
                save_to_sql(files,mode='replace')
            else:
                save_to_sql(files,mode='append')
        except :
            files.to_excel('.\上传失败\{}.xlsx'.format(stkcd))
            sql_fail(save_path,stkcd)
            print('{}上传失败'.format(stkcd))
        print('{}已上传至sql'.format(stkcd))